﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Sx.Data; 
using Sx.Core;
using System.Web;
using Sx.Utils;
using Microsoft.AspNetCore.Http;

namespace Sx.Core
{
    public partial class BaseService:IDisposable
    {
         
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="module">模块</param>
        /// <param name="tableName">表</param>
        /// <param name="business">业务</param>
        public BaseService(string module, string tableName, string business)
        {
            ModuleName = module;
            _tblName = tableName;
            _business = business;
            _db = GetDb(module); //new DbContext().ConnectionStringName(module, new SqlServerProvider()); 
        }
        #region 字段
        /// <summary>
        /// 表
        /// </summary>
        public string _tblName;
        /// <summary>
        /// 业务
        /// </summary>
        private string _business = "";
        private IDbContext _db; 
        #endregion

        #region 属性
        /// <summary>
        /// 是否需要添加日志
        /// </summary>
        public virtual bool NeedAddLog { get { return true; } }
        /// <summary>
        /// 模块
        /// </summary>
        public string ModuleName { get; set; }
        public IDbContext db
        {
            get
            {
                return _db;
            }
        } 
        #endregion
        
        public IDbContext GetDb(string connectstringName)
        {
            return GetDb(connectstringName, new PostgreSqlProvider());
        }
        public IDbContext GetDb(string connectstringName, IDbProvider provider)
        {
            return new DbContext().ConnectionStringName(connectstringName, provider);
        }

        public IDbContext GetDbByStr(string connectstring)
        {
            return GetDbByStr(connectstring, new SqlServerProvider());
        }
        public IDbContext GetDbByStr(string connectstring, IDbProvider provider) {
            return new DbContext().ConnectionString(connectstring, provider);
        }
        
        /// <summary>
        /// 插入
        /// </summary>
        /// <typeparam name="TID"></typeparam>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int Insert(object obj)
        {
            var columns = GetColumns(obj); 
            var sql = _db.Insert(_tblName);
            columns.ForEach(k=> sql.Column(k.Key,k.Value));
            return sql.Execute();
        }
        /// <summary>
        /// 插入
        /// </summary>
        /// <typeparam name="TID"></typeparam>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int Insert(string tblName,object obj)
        {
            var columns = GetColumns(obj);
            var sql = _db.Insert(tblName);
            columns.ForEach(k => sql.Column(k.Key, k.Value));
            return sql.Execute();
        }
        /// <summary>
        /// 插入并返回Id
        /// </summary>
        /// <typeparam name="TID"></typeparam>
        /// <param name="obj"></param>
        /// <returns></returns>
        public TID InsertReturnId<TID>(object obj) {
            var columns = GetColumns(obj);
            var sql = _db.Insert(_tblName);
            columns.ForEach(k => sql.Column(k.Key, k.Value));
            return sql.ExecuteReturnLastId<TID>();
        }

        /// <summary>
        /// 插入并返回Id
        /// </summary>
        /// <typeparam name="TID"></typeparam>
        /// <param name="obj"></param>
        /// <returns></returns>
        public TID InsertReturnId<TID>(string tblName, object obj)
        {
            var columns = GetColumns(obj);
            var sql = _db.Insert(tblName);
            columns.ForEach(k => sql.Column(k.Key, k.Value));
            return sql.ExecuteReturnLastId<TID>();
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="col"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public int Update(object colObj, object whereObj) {
            var update = _db.Update(_tblName);
            var columns = GetColumns(colObj);
            var wheres = GetColumns(whereObj);
            columns.ForEach(kvp => { update.Column(kvp.Key, kvp.Value); });
            wheres.ForEach(kvp => { update.Where(kvp.Key, kvp.Value); });
            return update.Execute();
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="col"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public int Update(string tblName, object colObj, object whereObj)
        {
            var update = _db.Update(tblName);
            var columns = GetColumns(colObj);
            var wheres = GetColumns(whereObj);
            columns.ForEach(kvp => { update.Column(kvp.Key, kvp.Value); });
            wheres.ForEach(kvp => { update.Where(kvp.Key, kvp.Value); });
            return update.Execute();
        }
        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="col"></param>
        /// <param name="where"></param>
        /// <returns></returns>
        public int Update(object colObj, string where)
        {
            var update = _db.Update(_tblName);
            var columns = GetColumns(colObj); 
            columns.ForEach(kvp => { update.Column(kvp.Key, kvp.Value); });
            update.Where(where);
            return update.Execute();
        }

        public int Update(string sql, object para) {
            return Execute(sql, para);
        }

        public int Execute(string sql, object para)
        {
            var columns = GetColumns(para);
            var _sql = _db.Sql(sql);
            columns.ForEach(kvp => { _sql.Parameter(kvp.Key, kvp.Value); });
            return _sql.Execute();
        }
        public T ExecuteReturnLastId<T>(string sql, object para)
        {
            var columns = GetColumns(para);
            var _sql = _db.Sql(sql);
            columns.ForEach(kvp => { _sql.Parameter(kvp.Key, kvp.Value); });
            return _sql.ExecuteReturnLastId<T>();
        }
        public int DeleteBySql(string sql, object where)
        {
            IDictionary<string, object> columns = GetColumns(where);
            var _sql = _db.Sql(sql);
            columns.ForEach(kvp => { _sql.Parameter(kvp.Key, kvp.Value); });
            return _sql.Execute();
        }
        public int Delete(string tblName, object where)
        {
            IDictionary<string, object> columns = GetColumns(where);
            var _sql = _db.Delete(tblName);
            columns.ForEach(kvp => { _sql.Where(kvp.Key, kvp.Value); });
            return _sql.Execute();
        }
        public int SaveByForm(HttpRequest request, 
            string updateColumns,
            string whereColumns,
            string id,
            Func<Dictionary<string, object>, bool> checkIsInsert = null)
        {
            var parameters = GetParamFromForm(request, id, checkIsInsert);
            if (parameters.CheckIsInsert())
            {
                var data = parameters.GetColumns(updateColumns);
                data.create_time = DateTime.Now;
                data.create_id = AuthHelper.GetUserid();
                data.update_time = DateTime.Now;
                data.update_id = AuthHelper.GetUserid();
                return Insert(data);
            }
            else
            {
                var data = parameters.GetColumns(updateColumns);
                data.update_time = DateTime.Now;
                data.update_id = AuthHelper.GetUserid();
                var where = parameters.GetWhere(whereColumns);
                return Update(data, where);
            }
        }

        public int SaveByForm(HttpRequest request,
            string insertColumns ,
            string updateColumns,
            string whereColumns,
            string id, 
            Func<Dictionary<string, object>, bool> checkIsInsert = null) {
            var parameters = GetParamFromForm(request, id, checkIsInsert);
            if (parameters.CheckIsInsert())
            {
                var data = parameters.GetColumns(insertColumns);
                data.create_time = DateTime.Now;
                data.create_id = AuthHelper.GetUserid();
                data.update_time = DateTime.Now;
                data.update_id = AuthHelper.GetUserid();
                return Insert(data);
            }
            else
            {
                var data = parameters.GetColumns(updateColumns);
                data.update_time = DateTime.Now;
                data.update_id = AuthHelper.GetUserid();
                var where = parameters.GetWhere(whereColumns);
                return Update(data, where);
            }
        }
        public int InsertByForm(HttpRequest request, string insertColumns = "" )
        {
            var parameters = GetParamFromForm(request);
            var data = parameters.GetColumns(insertColumns);
            data.create_time = DateTime.Now;
            data.create_id = AuthHelper.GetUserid();
            data.update_time = DateTime.Now;
            data.update_id = AuthHelper.GetUserid();
            return Insert(data);
        }
        public int UpdateByForm(HttpRequest request, string updateColumns = "",
            string whereColumns = "" ) {
            var parameters = GetParamFromForm(  request);
            var data = parameters.GetColumns(updateColumns);
            data.update_time = DateTime.Now;
            data.update_id = AuthHelper.GetUserid();
            var where = parameters.GetWhere(whereColumns);
            return Update(data, where);
        }
        public int Delete(object where) {
            var _sql = _db.Delete(_tblName);
            IDictionary<string, object> columns = GetColumns(where); 
            columns.ForEach(kvp => { _sql.Where(kvp.Key, kvp.Value); });
            return _sql.Execute();
        }
        public int DeleteByWhere(string where)
        {
            var _sql = _db.Delete(_tblName).Where(where);  
            return _sql.Execute();
        }
        public dynamic Single(string column, object where)
        { 
            IDictionary<string, object> columns = GetColumns(where); 
            var sql = _db.Select<dynamic>(column);
            sql.From(_tblName);
            columns.ForEach(k => {
                sql.AndWhere(string.Format(" {0} =@{0} ", k.Key));
                sql.Parameter(k.Key, k.Value);
            });
            return sql.QuerySingle();
        }
        public dynamic Single(object where)
        {

            IDictionary<string, object> columns = GetColumns(where);
            var sql = _db.Select<dynamic>("*");
            sql.From(_tblName);
            columns.ForEach(k => {
                sql.AndWhere(string.Format(" {0} =@{0} ", k.Key));
                sql.Parameter(k.Key, k.Value);
            });
            return sql.QuerySingle();
        }
        #region Exists
        public bool Exists(object where)
        {
            return Exists(this._tblName, where);
        } 
        public bool Exists(string tblName, object where)
        {

            IDictionary<string, object> columns = GetColumns(where);
            var sql = _db.Select<int>("count(1)");
            sql.From(tblName);
            columns.ForEach(k => {
                sql.AndWhere(string.Format(" {0} =@{0} ", k.Key));
                sql.Parameter(k.Key, k.Value);
            });
            return sql.QuerySingle() > 0;
        }
        #endregion

        #region Count
        public int Count(object where)
        {
            return Count(_tblName, where);
        } 
        public int Count(string tblName, object where)
        {

            IDictionary<string, object> columns = GetColumns(where);
            var sql = _db.Select<int>("count(1)");
            sql.From(tblName);
            columns.ForEach(k => {
                sql.AndWhere(string.Format(" {0} =@{0} ", k.Key));
                sql.Parameter(k.Key, k.Value);
            });
            return sql.QuerySingle();
        }
        #endregion

        public List<T> GetList<T>(  string where = "", string select = "*", string order = "")
        {
            return GetList<T>(_tblName,where,select,order);
        }
        public List<T> GetList<T>( object where = null, string select = "*", string order = "")
        { 
            return GetList<T>(_tblName, where, select, order);
        }

        public List<T> GetList<T>(string from, string where = "", string select = "*", string order = "")
        {
            var sql = _db.Select<T>(select);
            sql.From(from);
            sql.Where(where);
            if (order.IsNotNullOrEmpty())
            {
                sql.OrderBy(order);
            }
            return sql.QueryMany();
        }
        public List<T> GetList<T>(string from, object where = null, string select = "*", string order = "")
        { 
            var sql = _db.Select<T>(select);
            sql.From(from);
            if (where != null)
            {
                IDictionary<string, object> columns = GetColumns(where);
                columns.ForEach(k => {
                    sql.AndWhere(string.Format(" {0} =@{0} ", k.Key));
                    sql.Parameter(k.Key, k.Value);
                });
            }
            if (order.IsNotNullOrEmpty())
            {
                sql.OrderBy(order);
            }
            return sql.QueryMany();
        }
        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="columns"></param>
        /// <param name="table"></param>
        /// <param name="where"></param>
        /// <param name="sort"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public PageResult Page(string columns, string where,string defaultSort)
        {
            
            var result = new PageResult();
            result.rows = _db.Select<dynamic>(columns).From(_tblName).Where(where).OrderBy(CommonHelper.GetSort(defaultSort)).Paging(CommonHelper.GetPageIndex(), CommonHelper.GetPageRows()).QueryMany<List<dynamic>>();
            if (where.IsNotNullOrEmpty())
            {
                where = " where " + where;
            }
            result.total = _db.Sql(" select count(1) from " + _tblName + where).QuerySingle<long>();
            return result;
        }
        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="columns"></param>
        /// <param name="table"></param>
        /// <param name="where"></param>
        /// <param name="sort"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public PageResult Page(string columns, string table, string where, string defaultSort)
        {
            var result = new PageResult();
            result.rows = _db.Select<dynamic>(columns).From(table).Where(where).OrderBy(CommonHelper.GetSort(defaultSort)).Paging(CommonHelper.GetPageIndex(), CommonHelper.GetPageRows()).QueryMany<List<dynamic>>();
            if (where.IsNotNullOrEmpty())
            {
                where = " where " + where;
            }
            result.total = _db.Sql(" select count(1) from " + table + where).QuerySingle<long>();
            return result;
        }
        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="columns"></param>
        /// <param name="table"></param>
        /// <param name="where"></param>
        /// <param name="sort"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public PageResult Page(string columns, string table, string where, string sort, int page, int rows) {
            var  result = new PageResult();
            result.rows = _db.Select<dynamic>(columns).From(table).Where(where).OrderBy(sort).Paging(page, rows).QueryMany<List<dynamic>>();
            if (where.IsNotNullOrEmpty())
            {
                where = " where " + where;
            }
            result.total = _db.Sql(" select count(1) from " + table + where).QuerySingle<long>();
            return result;
        }

        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="columns"></param>
        /// <param name="table"></param>
        /// <param name="where"></param>
        /// <param name="sort"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public PageResult Page(string columns, string table, string where, string sort, int page, int rows,string connectString)
        {
            return Page(columns, table,(object) where, sort, page, rows, connectString);
        }
        /// <summary>
        /// 分页
        /// </summary>
        /// <param name="columns"></param>
        /// <param name="table"></param>
        /// <param name="where"></param>
        /// <param name="sort"></param>
        /// <param name="page"></param>
        /// <param name="rows"></param>
        /// <returns></returns>
        public PageResult Page(string columns, string table, Object where, string sort, int page, int rows, string connectString)
        {
            var result = new PageResult();
            var db = GetDbByStr(connectString);
            var selectList = db.Select<dynamic>(columns).From(table);
            var selectTotal = db.Select<long>("count(1)").From(table);
            if (where != null)
            {
                if (where is string && where.ToString().IsNotNullOrEmpty())
                {
                    selectList.AndWhere(where.ToString());
                    selectTotal.AndWhere(where.ToString());
                }
                else
                {
                    IDictionary<string, object> _columns = GetColumns(where);

                    _columns.ForEach(k => {
                        selectList.AndWhere(string.Format(" {0} =@{0} ", k.Key));
                        selectList.Parameter(k.Key, k.Value);
                        selectTotal.AndWhere(string.Format(" {0} =@{0} ", k.Key));
                        selectTotal.Parameter(k.Key, k.Value);
                    });
                }
            }
            
            result.rows = selectList.OrderBy(sort).Paging(page, rows).QueryMany<List<dynamic>>(); 
            result.total = selectTotal.QuerySingle();
            db.Dispose();
            return result;
        }
        public void Dispose()
        {
            if (_db != null)
            {
                _db.Dispose();
                _db = null;
            }
            if (true)
            {

            }
        }


        /// <summary>
        /// Trans the excute.
        /// </summary>
        /// <returns>System.Int32.</returns>
        public void TranExcute() {
            var db = _db.UseTransaction(true);
        }
    }
}
